Excel BI - Excel Challenge 717

excel-challenges
excel-formulas
🔰 Answer Expected Numbers Align 1st number in column 1, next 2 numbers into column 2 and so on and finally remaining numbers when columns exhaust after following this pattern..
Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 717

Challenge Description

🔰 Answer Expected Numbers Align 1st number in column 1, next 2 numbers into column 2 and so on and finally remaining numbers when columns exhaust after following this pattern.. Column headers 1, 2….should also be derived not hard coded.

Solutions

library(tidyverse)
library(readxl)

path = "Excel/700-799/717/717 Alignment.xlsx"
input = read_excel(path, range = "A2:A20")
test = read_excel(path, range = "B2:G7")

x = input$Numbers
sizes = accumulate(1:length(x), `+`) %>% detect_index(~ .x >= length(x))
splits = split(x, rep(1:sizes, 1:sizes)[1:length(x)])
max_len = max(lengths(splits))
result = map_dfc(splits, ~ tibble(c(.x, rep(NA, max_len - length(.x))))) %>%
  set_names(as.character(seq_along(splits)))

all.equal(result, test)
  • Logic: Read the workbook ranges needed for the challenge.
  • Strengths: The code maps the workbook rule into a compact, reproducible pipeline.
  • Areas for Improvement: The solution assumes the workbook layout and selected ranges remain stable, so any structural change in the sheet would require small adjustments.
  • Gem: The elegant part is how little code is needed once the correct intermediate representation is chosen.
import pandas as pd
import numpy as np
from itertools import accumulate

path = "700-799/717/717 Alignment.xlsx"
input = pd.read_excel(path, usecols="A", skiprows=1, nrows=18)
test = pd.read_excel(path, usecols="B:G", skiprows=1, nrows=5)

x = input.iloc[:, 0].tolist()

sizes = next(i+1 for i, s in enumerate(accumulate(range(1, len(x)+1))) if s >= len(x))

split_indices = np.repeat(range(sizes), range(1, sizes+1))[:len(x)]
splits = [[] for _ in range(sizes)]
for idx, val in zip(split_indices, x):
    splits[idx].append(val)

max_len = max(len(s) for s in splits)
result = pd.DataFrame({str(i+1): s + [np.nan]*(max_len - len(s)) for i, s in enumerate(splits)})

print(all(result) == all(test)) # True

The Python version keeps the algorithm explicit, which helps when the challenge depends on a greedy or iterative rule.

Difficulty Level

Easy / Medium

The business rule is clear, though the workbook still needs a few transformation steps to reach the expected output.